﻿using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;

namespace KBZ.Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 将List转换成DataTable
        /// </summary>
        public static DataTable ToDataTable<T>(IList<T> items)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable dt = new DataTable();
            for (int i = 0; i < properties.Count; i++)
            {
                PropertyDescriptor property = properties[i];
                Type colType = property.PropertyType;
                if (colType.IsGenericType &&
                    colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                dt.Columns.Add(property.Name, colType);
            }
            object[] values = new object[properties.Count];
            foreach (T item in items)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }
                dt.Rows.Add(values);
            }
            return dt;
        }

        #region 导出Excel2003

        /// <summary>
        /// 导出Excel2003
        /// </summary>
        public static System.IO.Stream ExportToStream(System.Data.DataTable table)
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream();

            var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);

            IRow headRow = sheet.CreateRow(0);
            foreach (System.Data.DataColumn column in table.Columns)
            {
                headRow.CreateCell(column.Ordinal).SetCellValue(string.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption);
            }

            int rowIndex = 1;

            foreach (System.Data.DataRow row in table.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (System.Data.DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }
            workbook.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }

        /// <summary>
        /// 导出Excel2003
        /// </summary>
        public static byte[] ExportToBytes(System.Data.DataTable table)
        {
            byte[] bytes = null;

            using (var stream = ExportToStream(table))
            {
                bytes = new byte[stream.Length];
                stream.Read(bytes, 0, bytes.Length);
                stream.Seek(0, System.IO.SeekOrigin.Begin);
            }

            return bytes;
        }
        /// <summary>
        /// 导出Excel2003
        /// </summary>
        public static byte[] ExportStreamToBytes(System.IO.Stream stream)
        {
            byte[] bytes = null;
            bytes = new byte[stream.Length];
            stream.Read(bytes, 0, bytes.Length);
            stream.Seek(0, System.IO.SeekOrigin.Begin);
            stream.Dispose();
            return bytes;
        }

        #endregion

        #region 导出Excel2007

        /// <summary>
        /// 这是为了避免流被NPOI关闭而实现的流
        /// 当CanDispose为false时此流的dispose接口无效，仅当该值为true时有效
        /// </summary>
        public class ExcelStream : System.IO.MemoryStream
        {
            public ExcelStream()
            {
                this.CanDispose = true;
            }
            protected override void Dispose(bool disposing)
            {
                if (CanDispose)
                {
                    base.Dispose(disposing);
                }
            }

            public bool CanDispose { get; set; }
        }

        /// <summary>
        /// 导出Excel2007
        /// </summary>
        public static System.IO.Stream ExportToStream2007(System.Data.DataTable table)
        {
            var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);

            IRow headRow = sheet.CreateRow(0);
            foreach (System.Data.DataColumn column in table.Columns)
            {
                headRow.CreateCell(column.Ordinal).SetCellValue(string.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption);
            }

            int rowIndex = 1;

            foreach (System.Data.DataRow row in table.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (System.Data.DataColumn column in table.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            var stream = new ExcelStream();
            stream.CanDispose = false;
            workbook.Write(stream);
            stream.Seek(0, System.IO.SeekOrigin.Begin);
            stream.CanDispose = true;

            sheet = null;
            headerRow = null;
            workbook = null;

            return stream;
        }

        /// <summary>
        /// 导出Excel2007
        /// </summary>
        public static byte[] ExportToBytes2007(System.Data.DataTable table)
        {
            byte[] bytes = null;

            using (var stream = ExportToStream2007(table))
            {
                bytes = new byte[stream.Length];
                stream.Read(bytes, 0, bytes.Length);
                stream.Seek(0, System.IO.SeekOrigin.Begin);
            }

            return bytes;
        }

        #endregion
    }
}
